from openpyxl import load_workbook
import time
from openpyxl import Workbook
import xlrd


class generateRoadList():
    def __init__(self,filepath,savepath,tested_all):
        self.excel_road_list = []
        self.filepath = filepath
        self.savepath = savepath
        self.tested_all = tested_all
        self.acquire_sheet()
        self.tested_road_list = self.create_tested_road_list()

        self.wb = Workbook()
        self.ws = self.wb.active

        self.workbook_xlrd = xlrd.open_workbook(r'{}'.format(filepath))
        self.sheet_xlrd = self.workbook_xlrd.sheet_by_name('Sheet1')

    def acquire_sheet(self):
        self.workbook = load_workbook(self.filepath)
        self.sheet = self.workbook["Sheet1"]  # 根据表名获取要操作的sheet页

    # 生成excel中的行号和路口值[{2:596}{}{}]
    def create_excel_road_item_list(self):
        cells = self.sheet["A"]
        row_num = 0
        for i in cells:
            row_num += 1
            if i.value == 'crossId':
                continue
            self.excel_road_list.append({row_num: i.value})
        return self.excel_road_list

    # 获取测试过的路口
    def create_tested_road_list(self):
        tested_road = self.tested_all.split(' ')
        return tested_road

    # 保存excel
    def savePath(self):
        self.workbook.save(savepath)

    # 删除excel一行，更新行号，循环执行
    def loop_update_excel(self):
        excel_road_item_list = self.create_excel_road_item_list()
        for item in excel_road_item_list:
            # print(item)
            current_excel_index = list(item.keys())[0]
            current_excel_vlaue = list(item.values())[0]
            # print(current_excel_index, current_excel_vlaue)
            if str(current_excel_vlaue) in self.tested_road_list:
                self.ws.append(self.sheet_xlrd.row_values(current_excel_index))
                self.sheet.delete_rows(current_excel_index)  # 删除当前行
                # 删掉一行后，重新组装excel的路口号和索引
                if (current_excel_index-1)==len(excel_road_item_list):
                    # 如果是excel最后一个值是测试过的路口，直接返回
                    return
                self.excel_road_list.clear()
                self.loop_update_excel()
        # print(excel_road_item_list)

    def main(self):
        self.loop_update_excel()
        self.savePath()


if __name__ == '__main__':
    filepath = r"/Users/v_baiguanghui/Desktop/苏州出差/稳定性excel/356全量路口.xlsx"
    savepath = r'/Users/v_baiguanghui/Desktop/苏州出差/result/result_'+str(time.strftime("%Y-%m-%d_%H_%M_%S",time.localtime(time.time())))+'.xlsx'

    # 研发自测路口
    tested_base = '5 17 21 22 25 27 29 45 47 56 59 65 77 82 85 103 105 126 138 154 174 185 186 189 190 203 207 215 216 218 223 256 261 263 267 287 295 319 337 347 358 372 373 376 377 380 381 387 390 394 410 424 433 438 445 448 473 479 488 490 494 496 498 522 526 531 532 535 546 567 '
    # QA已测试路口
    tested_road_19 = '1 78 127 144 165 210 241 244 283 296 299 308 325 370 401 402 426 475 495 550 551 553 '
    tested_road_20 = '19 46 50 51 80 86 107 112 120 133 171 192 196 199 213 226 231 240 247 255 269 272 277 279 286 301 316 329 338 367 392 399 422 423 463 470 476 484 491 497 502 508 519 566 572 '
    tested_road_21 = '24 30 36 58 83 109 114 122 130 153 159 164 181 182 202 229 242 258 260 302 310 333 346 350 357 362 383 400 405 408 414 439 457 461 500 504 506 507 544 547 549 558 560 '
    tested_road_22 = '334 343 573 248 492 147 379 542 41 158 305 91 257 441 363 554 419 285 474 446 321 526 409 394 137 485 525 256 186 211 176 111 375 488 65 377 218 237 427 382 395 578 276 223 380 207 223 445 267 190 531 468 149 320 154 85 139 45 59 569 168 418 561 415 77 103 117 '
    tested_road_23 = '37 55 66 96 98 101 102 116 136 151 166 186 201 207 218 256 267 313 331 358 386 394 410 412 413 431 445 450 464 466 486 493 514 516 517 518 520 530 570'
    tested_all = tested_base + tested_road_19 + tested_road_20 + tested_road_21 + tested_road_22 + tested_road_23
    obj = generateRoadList(filepath,savepath,tested_all)
    obj.main()


    # obj.wb.save(savepath)